﻿/***************************************************************
 * 开发人员：网魂小兵@http://www.mozlite.com
 * 
 * 目标标本：4.0.30319.239
 * 
 * 最后修改：2011/11/11 21:59:12
 * ************************************************************/
namespace Mozlite.Data.MySqlDataProvider
{
    using System;
    using System.Linq;
    using System.Text;
    using System.Data;
    using System.Data.Common;
    using System.Collections.Generic;
    using Mozlite.FileStorages;
    using MySql.Data.MySqlClient;

    /// <summary>
    /// 数据库提供者。
    /// </summary>
    public class MySqlFileStorageDataProvider : FileStorageDataProvider
    {
        /// <summary>
        /// 初始化类<see cref="T:Mozlite.Data.MySqlDataProvider.MySqlFileStorageDataProvider"/>。
        /// </summary>
        public MySqlFileStorageDataProvider() : base(MySqlClientFactory.Instance) { }

        public override FileStorageStatus CreateFolder(UserFolder folder)
        {
            if (ExecuteNonQuery("INSERT INTO {0}(UserID, ParentID, FolderName, CreateDate, LastModified, ContentLength, FileCount)VALUES('{1}', '{2}', '{3}', now(), now(), '0', '0');", AddProfix("UserFolders"), folder.UserID, folder.ParentID, Q(folder.FolderName)))
                return FileStorageStatus.Created;
            return FileStorageStatus.UnknownError;
        }

        public override Dictionary<int, UserFolder> GetFolders(int userID)
        {
            var folders = new Dictionary<int, UserFolder>();
            using (var reader = ExecuteReader("SELECT * FROM {0} WHERE UserID = '{1}';", AddProfix("UserFolders"), userID)) {
                while (reader.Read())
                    folders.Add(reader.GetInt32("FolderID"), new UserFolder(reader));
                reader.Close();
            }
            return folders;
        }

        public override FileStorageStatus SaveFile(UserFile file)
        {
            if (file.FileID > 0)
            {
                if (ExecuteNonQuery("UPDATE {0} SET FolderID = '{1}', FileName = '{2}', Extension = '{3}', LastModified = now() WHERE FileID = '{4}';", AddProfix("UserFiles"), file.FolderID, Q(file.FileName), Q(file.Extension), file.FileID))
                    return FileStorageStatus.Updated;
            }
            else
            {
                    var transaction = GetTransaction();
                try
                {
                    ExecuteNonQuery(transaction, string.Format("INSERT IGNORE INTO {0}(`DiskFileID`,`ContentType`,`ContentLength`,`RefererCount`)VALUES('{1}', '{2}', '{3}', '0');", AddProfix("Files"), file.DiskFileID, Q(file.ContentType), file.ContentLength));
                    ExecuteNonQuery(transaction, string.Format("INSERT INTO {0}(`FolderID`,`UserID`,`FileName`,`Extension`,`DiskFileID`,`CreateDate`,`LastModified`,FileType)VALUES('{1}','{2}','{3}','{4}','{5}',now(),now(),'{6}');", AddProfix("UserFiles"), file.FolderID, file.UserID, Q(file.FileName), Q(file.Extension), Q(file.DiskFileID), Q(file.FileType)));
                    file.FileID = ExecuteScalar<int>(transaction, "select last_insert_id();");
                    transaction.CommitTransaction();
                    return FileStorageStatus.Created;
                }
                catch { transaction.RollbackTransaction(); }
            }
            return FileStorageStatus.UnknownError;
        }

        public override Dictionary<int, List<UserFile>> LoadFiles(int userID)
        {
            var files = new Dictionary<int, List<UserFile>>();
            using (var reader = ExecuteReader("SELECT `FileID`,FolderID,`UserID`,`FileName`,`Extension`, f.`DiskFileID`,`ContentType`,`ContentLength`,`RefererCount`,`CreateDate`,`LastModified`,FileType FROM {0} f INNER JOIN {1} uf ON f.DiskFileID = uf.DiskFileID WHERE UserID = {2};", AddProfix("Files"), AddProfix("UserFiles"), userID))
            {
                while (reader.Read())
                {
                    var userFile = new UserFile(reader);
                    if (!files.ContainsKey(userFile.FolderID))
                        files.Add(userFile.FolderID, new List<UserFile>());
                    files[userFile.FolderID].Add(userFile);
                }
                reader.Close();
            }
            return files;
        }

        public override FileStorageStatus ChangeFolderName(int folderID, int userID, string folderName)
        {
            if (ExecuteNonQuery("UPDATE {0} SET FolderName = '{1}', LastModified = now() WHERE FolderID = '{2}' AND UserID = '{3}';", AddProfix("UserFolders"), Q(folderName), folderID, userID))
                return FileStorageStatus.Updated;
            return FileStorageStatus.UnknownError;
        }

        public override FileStorageStatus MoveFolder(int srcID, int desID, int userID)
        {
            if (ExecuteNonQuery("UPDATE {0} SET ParentID = '{1}' WHERE FolderID = '{2}' AND UserID = '{3}';", AddProfix("UserFolders"), desID, srcID, userID))
                return FileStorageStatus.Moved;
            return FileStorageStatus.UnknownError;
        }

        public override long GetDiskUsage(int userID)
        {
            return ExecuteScalar<long>("SELECT SUM(ContentLength) FROM {0} f INNER JOIN {1} uf ON f.DiskFileID = uf.DiskFileID WHERE UserID = '{2}';", AddProfix("Files"), AddProfix("UserFiles"), userID);
        }

        public override UserFile GetFile(int userID, int fileID)
        {
            return ExecuteReader("SELECT `FileID`,FolderID,`UserID`,`FileName`,`Extension`, f.`DiskFileID`,`ContentType`,`ContentLength`,`RefererCount`,`CreateDate`,`LastModified`,FileType FROM {0} f INNER JOIN {1} uf ON f.DiskFileID = uf.DiskFileID WHERE UserID = {2} AND FileID = {3};", AddProfix("Files"), AddProfix("UserFiles"), userID, fileID).TGet<UserFile>();
        }

        public override FileStorageStatus MoveFile(int fileID, int folderID, int userID)
        {
            if (ExecuteNonQuery("UPDATE {0} SET FolderID = '{1}' WHERE FileID = '{2}' AND UserID = '{3}';", AddProfix("UserFiles"), folderID, fileID, userID))
                return FileStorageStatus.Moved;
            return FileStorageStatus.UnknownError;
        }

        public override void CalculateFolderSize()
        {
            ExecuteNonQuery("UPDATE {0} SET ContentLength = (SELECT SUM(ContentLength) FROM {1} f INNER JOIN {2} uf ON f.DiskFileID = uf.DiskFileID WHERE {0}.FolderID = uf.FolderID)", AddProfix("UserFolders"), AddProfix("Files"), AddProfix("UserFiles"));
        }

        public override bool DeleteFolder(int userID, int folderID)
        {
            return ExecuteNonQuery("DELETE FROM {0} WHERE UserID = {1} AND FolderID = {2};", AddProfix("UserFolders"), userID, folderID);
        }

        public override bool DeleteFile(int userID, int fileID)
        {
            return ExecuteNonQuery("DELETE FROM {0} WHERE UserID = {1} AND FileID = {2};", AddProfix("UserFiles"), userID, fileID);
        }

        public override UserFile GetStorage(int fileID)
        {
            return ExecuteReader("SELECT `FileID`,FolderID,`UserID`,`FileName`,`Extension`, f.`DiskFileID`,`ContentType`,`ContentLength`,`RefererCount`,`CreateDate`,`LastModified`,FileType FROM {0} f INNER JOIN {1} uf ON f.DiskFileID = uf.DiskFileID WHERE FileID = {2};", AddProfix("Files"), AddProfix("UserFiles"), fileID).TGet<UserFile>();
        }
    }
}